const express = require('express');
const router = express.Router();
const stuModel = require('../model/waterModel');
const DB = require("../config/dbconfig"); // 导入数据库配置文件
const Sequelize = require("sequelize");
const dayjs = require('dayjs')

// 获取所有井信息：http://localhost:3000/water/searchone
router.get('/search', async (req, res, next) => {
    try {
        const result = await stuModel.findAll({ raw: true });
        res.json({
            code: 200,
            msg: "查询成功",
            data: result
        });
    } catch (err) {
        next(err);
    }
});

// 获取单井信息：http://localhost:3000/water/searchone/七里013-H1
router.get('/searchone/:well_num', async (req, res, next) => {
    try {
        const well_num = req.params.well_num;
        // console.log(`output->well_num`,well_num)
        const well_first = well_num.split('_')[0];
        const well_second = well_num.split('_')[1];
        // console.log(`output->`, req)
        const sql = "SELECT * FROM new_data_processed where 井号 = ? and 层位=? order by 生产日期 asc";
        const result = await DB.query(sql, { replacements: [well_first, well_second], type: Sequelize.QueryTypes.SELECT });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "查询成功",
            data: result
        });
    } catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }
});

// 获取所有井井号预览信息：http://localhost:3000/water/preview
router.get('/preview', async (req, res, next) => {
    try {

        const sql = `SELECT DISTINCT CONCAT(井号, '_', 层位 ) AS 'value' FROM well_index_classification`
        const result = await DB.query(sql, { type: Sequelize.QueryTypes.SELECT });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "查询成功",
            data: result
        });
    } catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }
});

router.get('/goodPreview', async (req, res, next) => {
    try {
        const sql = `SELECT DISTINCT CONCAT(井号, '_', 层位 ) AS 'value' FROM well_index_classification where 类别 = '0' and isWater='0'`
        const result = await DB.query(sql, { type: Sequelize.QueryTypes.SELECT });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "查询成功",
            data: result
        });
    } catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }
});
router.get('/badPreview', async (req, res, next) => {
    try {
        const sql = `SELECT DISTINCT CONCAT(井号, '_', 层位 ) AS 'value' FROM well_index_classification where 类别 = '1' and isWater='0'`
        const result = await DB.query(sql, { type: Sequelize.QueryTypes.SELECT });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "查询成功",
            data: result
        });
    } catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }
});

router.get('/GoodOrBadOrValidPreview/:well_class', async (req, res, next) => {
    try {
        const well_class = req.params.well_class; // 0 is good 1 is bad 2 is valid
        const sql = `SELECT DISTINCT CONCAT(井号, '_', 层位 ) AS 'value' FROM well_index_classification where 类别 = ? and isWater='1'`
        const result = await DB.query(sql, { replacements: [well_class],type: Sequelize.QueryTypes.SELECT });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "查询成功",
            data: result
        });
    } catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }
});
router.get('/addToGood/:well_num', async (req, res, next) => {
    try {
        const well_num = req.params.well_num;
        const well_first = well_num.split('_')[0];
        const well_second = well_num.split('_')[1];
        console.log(`output->well_num`, well_num)
        const currentTime = dayjs().format('YYYY-MM-DD HH:mm:ss');
        const sql = "UPDATE well_index_classification SET 类别 = '0',修改时间=? WHERE 井号= ? AND 层位 = ? ";
        const result = await DB.query(sql, { replacements: [currentTime, well_first, well_second], type: Sequelize.QueryTypes.UPDATE });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "修改成功",
            data: result
        });
    } catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }
});

router.get('/addToBad/:well_num', async (req, res, next) => {
    try {
        const well_num = req.params.well_num;
        const well_first = well_num.split('_')[0];
        const well_second = well_num.split('_')[1];
        console.log(`output->well_num`, well_num)
        const currentTime = dayjs().format('YYYY-MM-DD HH:mm:ss');
        const sql = "UPDATE well_index_classification SET 类别 = '1',修改时间=?  WHERE 井号= ? AND 层位 = ? ";
        const result = await DB.query(sql, { replacements: [currentTime, well_first, well_second], type: Sequelize.QueryTypes.UPDATE });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "修改成功",
            data: result
        });
    } catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }
});



// UPDATE water_data_denosing SET label = 1 WHERE 井号 = "七里013-H1" AND 层位 = "石炭系"
//  AND 生产日期 BETWEEN "2010-09-18" AND "2010-09-24";
// 更新井的积液标签：http://localhost:3000/water/updateLabel
router.post('/updateLabel', async (req, res, next) => {
    const t = await DB.transaction();
    try {
        const { label, well_num, cengwei_num, start_time, end_time } = req.body;
        console.log(`output->`, label, well_num, cengwei_num, start_time, end_time)
        const sql1 = "UPDATE new_data_processed SET label = ? WHERE 井号= ? AND 层位 = ? AND 生产日期 BETWEEN ? AND ?";
        const sql2 = "UPDATE origin_data_merged SET label = ? WHERE 井号= ? AND 层位 = ? AND 生产日期 BETWEEN ? AND ?";
        await DB.query(sql1, {
            replacements: [label, well_num, cengwei_num, start_time, end_time],
            type: Sequelize.QueryTypes.UPDATE
        });
        await DB.query(sql2, {
            replacements: [label, well_num, cengwei_num, start_time, end_time],
            type: Sequelize.QueryTypes.UPDATE
        });
        const result = await t.commit();
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "标签更新成功",
            data: result
        });
    } catch (err) {
        await t.rollback();
        // console.log(`output->`, req.query)
        next(err);
    }
});

router.post('/usefulLabel', async (req, res, next) => {
    const t = await DB.transaction();
    try {
        const {label, well_num, cengwei_num, start_time, end_time } = req.body;
        console.log(`output->`,  well_num, cengwei_num, start_time, end_time)
        const sql1 = "UPDATE new_data_processed SET 可用时间段 = ? WHERE 井号= ? AND 层位 = ? AND 生产日期 BETWEEN ? AND ?";
        const sql2 = "UPDATE origin_data_merged SET 可用时间段 = ? WHERE 井号= ? AND 层位 = ? AND 生产日期 BETWEEN ? AND ?";
        await DB.query(sql1, {
            replacements: [label, well_num, cengwei_num, start_time, end_time],
            type: Sequelize.QueryTypes.UPDATE
        });
        await DB.query(sql2, {
            replacements: [label,well_num, cengwei_num, start_time, end_time],
            type: Sequelize.QueryTypes.UPDATE
        });
        const result = await t.commit();
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "标签更新成功",
            data: result
        });
    } catch (err) {
        await t.rollback();
        // console.log(`output->`, req.query)
        next(err);
    }
});

// 处理异常噪音数据
router.post('/denose', async (req, res, next) => {
    const t = await DB.transaction();
    try {
        const { well_num, cengwei_num, start_time } = req.body;
        const sql1 = `
                SELECT
                    round(AVG( 日产气), 4) AS avg_gas,
                    round(AVG( 日产水), 4) AS avg_water,
                    round(AVG( 平均油压), 4) AS avg_gaspress,
                    round(AVG( 平均套压), 4) AS avg_tpress,
                    round(AVG( 输压), 4) AS avg_outpress,
                    round(AVG( 套油压差), 4) AS avg_tydiff,
                    round(AVG( 水气比), 4) AS avg_wgratio,
                    round(AVG( 油压变化速率), 4) AS avg_gvarspeed
                FROM
                    new_data_processed
                WHERE
                    井号 = ? AND 层位 = ?`;
        const result1 = await DB.query(sql1, {
            replacements: [well_num, cengwei_num,],
            type: Sequelize.QueryTypes.SELECT
        });
        const sql2 = `UPDATE new_data_processed
                    SET
                        日产气 = ${result1[0].avg_gas},
                        日产水 = ${result1[0].avg_water},
                        平均油压 = ${result1[0].avg_gaspress},
                        平均套压 = ${result1[0].avg_tpress},
                        输压 = ${result1[0].avg_outpress},
                        套油压差 = ${result1[0].avg_tydiff},
                        水气比 = ${result1[0].avg_wgratio},
                        油压变化速率 = ${result1[0].avg_gvarspeed}
                    WHERE
                        new_data_processed.井号 = ?
                        AND new_data_processed.层位 = ?
                        AND new_data_processed.生产日期 = ?;`
        await DB.query(sql2, {
            replacements: [well_num, cengwei_num, start_time],
            type: Sequelize.QueryTypes.UPDATE
        });
        const result = await t.commit();
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "去噪成功",
            data: result
        });
    } catch (err) {
        await t.rollback();
        // console.log(`output->`, req.query)
        next(err);
    }
});
// 错误处理中间件
router.use((err, req, res, next) => {
    console.error(err);
    res.status(500).json({
        code: 1002,
        msg: '服务器发生错误',
    });
});

router.get('/waterOrNormalPreview/:isWater', async (req, res, next) => {
    try {
        const isWater = req.params.isWater;
        const sql = `SELECT DISTINCT CONCAT(井号, '_', 层位 ) AS 'value' 
        FROM well_index_classification where isWater = ? and 类别 = 0 `
        const result = await DB.query(sql, { replacements: [isWater], type: Sequelize.QueryTypes.SELECT });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "查询成功",
            data: result
        });
    } catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }
});
router.get('/addToWaterOrNormal/:well_num', async (req, res, next) => {
    try {
        const well_num = req.params.well_num;
        const well_first = well_num.split('_')[0];
        const well_second = well_num.split('_')[1];
        const isWater = well_num.split('_')[2];
        const currentTime = dayjs().format('YYYY-MM-DD HH:mm:ss');
        const sql = `UPDATE well_index_classification SET 类别 = 0, isWater=?,修改时间=? WHERE 井号= ? AND 层位 = ? `;
        const result = await DB.query(sql, { replacements: [isWater, currentTime, well_first, well_second], type: Sequelize.QueryTypes.UPDATE });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "修改成功",
            data: result
        });
    } catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }
});
// stop
router.get('/addToStopOrNostop/:well_num', async (req, res, next) => {
    try {
        const well_num = req.params.well_num;
        const well_first = well_num.split('_')[0];
        const well_second = well_num.split('_')[1];
        const isWater = well_num.split('_')[2];
        const currentTime = dayjs().format('YYYY-MM-DD HH:mm:ss');
        const sql = `UPDATE well_index_classification SET 类别 = 0, isStop=?,修改时间=? WHERE 井号= ? AND 层位 = ? `;
        const result = await DB.query(sql, { replacements: [isWater, currentTime, well_first, well_second], type: Sequelize.QueryTypes.UPDATE });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "修改成功",
            data: result
        });
    } catch (err) {
        console.log(`output->`, req.params.well_num)
        next(err);
    }
});

router.get('/showStopOrNostop/:isStop', async (req, res, next) => {
    try {
        const isStop = req.params.isStop;
        const sql = `SELECT DISTINCT CONCAT(井号, '_', 层位 ) AS 'value' 
        FROM well_index_classification where isStop = ? and 类别 = 0 `
        const result = await DB.query(sql, { replacements: [isStop], type: Sequelize.QueryTypes.SELECT });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "查询成功",
            data: result
        });
    } catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }
});

router.get('/addToValid/:well_num', async (req, res, next) => {
    try {
        const well_num = req.params.well_num;
        const well_first = well_num.split('_')[0];
        const well_second = well_num.split('_')[1];
        const isWater = well_num.split('_')[2];
        const currentTime = dayjs().format('YYYY-MM-DD HH:mm:ss');
        const sql = `UPDATE well_index_classification SET 类别 = ?, isWater=1,修改时间=? WHERE 井号= ? AND 层位 = ? `;
        const result = await DB.query(sql, { replacements: [isWater, currentTime, well_first, well_second], type: Sequelize.QueryTypes.UPDATE });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "修改成功",
            data: result
        });
    } catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }
});

// 添加到正常数据集 用于异常检测模型训练
router.get('/addToNorm/:well_num', async (req, res, next) => {
    try {
        const well_num = req.params.well_num;
        const well_first = well_num.split('_')[0];
        const well_second = well_num.split('_')[1];
        const currentTime = dayjs().format('YYYY-MM-DD HH:mm:ss');
        const sql = `UPDATE well_index_classification SET 类别 = 0,isWater=0, isNorm=1,修改时间=? WHERE 井号= ? AND 层位 = ? `;
        const result = await DB.query(sql, { replacements: [currentTime, well_first, well_second], type: Sequelize.QueryTypes.UPDATE });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "修改成功",
            data: result
        });
    } catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }
});

router.get('/AbNormalPreview/:isNorm', async (req, res, next) => {
    try {
        const isNorm = req.params.isNorm;
        const sql = `SELECT DISTINCT CONCAT(井号, '_', 层位 ) AS 'value' 
        FROM well_index_classification where isNorm = ? and 类别 = 0 `
        const result = await DB.query(sql, { replacements: [isNorm], type: Sequelize.QueryTypes.SELECT });
        // console.log(`output->`, result)
        res.json({
            code: 200,
            msg: "查询成功",
            data: result
        });
    } catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }
});


// 获取统计信息
router.get('/getStatisticsInfo', async (req, res, next) => {
    try {
        const sql = `SELECT
                        count(*) AS total_count,
                        SUM(CASE WHEN 类别 = 0 and isWater=1 THEN 1 ELSE 0 END) AS water_count,
                        SUM(CASE WHEN  类别 = 0 and isWater=0 THEN 1 ELSE 0 END) AS valid_count,
                        SUM(CASE WHEN  类别 = 0  and isWater=1 THEN 1 ELSE 0 END) AS warning_count
                    FROM
                        well_index_classification;`;
        const result = await DB.query(sql, { type: Sequelize.QueryTypes.SELECT })
        res.json({
            code: 200,
            msg: "修改成功",
            data: result
        });
    }
    catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }

});

router.get('/getWarningInfo', async (req, res, next) => {
    try {
        const sql = `select * from warning_info;`;
        const result = await DB.query(sql, { type: Sequelize.QueryTypes.SELECT })
        res.json({
            code: 200,
            msg: "数据加载成功",
            data: result
        });
    }
    catch (err) {
        // console.log(`output->`, req.query)
        next(err);
    }

});


module.exports = router;